#!/bin/bash

#
# @date:2021-10-10
# @author:deep as the sea 
# @orgnization:doitedu
# @desc: 空设备guid映射表更新计算脚本
#


export HIVE_HOME=/opt/apps/hive-3.1.2

dt=$(date -d'-1 day' +%Y-%m-%d)

if [ $1 ];then
echo "运行脚本，传入了指定的日期，将导指定日期的数据"
dt=$1
fi

dt_pre=$(date -d"${dt} -1day" +%Y-%m-%d)

${HIVE_HOME}/bin/hive -e "
add jar ${HIVE_HOME}/hcatalog/share/hcatalog/hive-hcatalog-core-3.1.2.jar;
WITH TMP AS (
  SELECT
    o1.device_id,
    o1.first_login,
    o1.last_login
  FROM 
  (
  SELECT
    deviceid as device_id,
    min(\`timestamp\`) as first_login,  -- 当日的最早访问时间
    max(\`timestamp\`) as last_login    -- 当日的最晚访问时间
  FROM  ods.mall_app_log_dtl
  WHERE dt='${dt}'
  GROUP BY deviceid
  HAVING max(if(trim(account) ='',null,account)) is null
  ) o1
  
  LEFT JOIN 
  
  (
    SELECT
       device_id
    FROM dim.dev_acc_bind_w
    WHERE dt='${dt}'
  ) o2
  
  ON o1.device_id=o2.device_id
  WHERE o2.device_id is NULL
)

--- 为上面找到的空设备去映射guid ------------------

INSERT INTO TABLE dim.dev_guid_map PARTITION(dt='${dt}')
SELECT
   o1.device_id,
   row_number() over() + o2.old_max  as guid,
   o1.first_login,
   o1.last_login
FROM
(
-- 真正的空设备，而且之前没映射过GUID
  SELECT
    tmp.device_id,
    tmp.first_login,
    tmp.last_login
  FROM 
  tmp
  LEFT JOIN
  (
     SELECT
       device_id   
     FROM  dim.dev_guid_map
     WHERE dt='${dt_pre}'
  ) o
  ON tmp.device_id=o.device_id
  WHERE o.device_id is null
) o1
CROSS JOIN
(
   SELECT nvl(max(guid),1000000000)as old_max from dim.dev_guid_map WHERE dt='${dt_pre}'
) o2


UNION ALL   

SELECT  
  device_id,
  guid,
  first_login,
  last_login  
FROM dim.dev_guid_map 
WHERE dt='${dt_pre}'
"


if [ $? -eq 0 ];then
  echo "任务成功：空设备guid映射表更新计算成功"
  echo "目标表分区：dim.dev_guid_map  partition(dt='${dt}')"
  echo "任务成功：空设备guid映射表更新计算成功；目标表分区：dim.dev_guid_map  partition(dt='${dt}')" | mail -s '多易集团,商城业务数据部，HIVE任务成功通知' 83544844@qq.com
  exit 0
else
  echo "任务失败：空设备guid映射表更新计算失败"
  echo "目标表分区：dim.dev_guid_map  partition(dt='${dt}')"
  echo "任务失败：空设备guid映射表更新计算失败；目标表分区：dim.dev_guid_map  partition(dt='${dt}')" | mail -s '多易集团,商城业务数据部，HIVE任务失败通知' 83544844@qq.com
  exit 0
fi  
